package com.myres.struts.action;
import com.myres.db.ConnectionPool;
import com.myres.model.Bulk;
import com.myres.model.Product;
import com.myres.model.Score;

import java.util.Date;
import java.util.Map;
import java.util.TreeMap;
import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
public class ScoreService {
	public List<Score> getScoreByUID(int uid){
		Connection con=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			con=ConnectionPool.getPool().getConnection();
			pstmt=con.prepareStatement("select * from res_scores where _user_id=? order by _add_time desc");
			pstmt.setInt(1, uid);
			rs=pstmt.executeQuery();
			return Score.populate(rs);
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				con.close();
			}catch(Exception e){
				
			}
			  
		}
	}
	
	public Bulk getBulgById(int id){
		Connection con=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			con=ConnectionPool.getPool().getConnection();
			pstmt=con.prepareStatement("select * from res_bulk where _id=?");
			pstmt.setInt(1, id);
			rs=pstmt.executeQuery();
			List<Bulk> bulks= populateBulk(rs);
			if(bulks.size()==0) return null;
			return bulks.get(0); 
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				con.close();
			}catch(Exception e){
				
			}	  
		}
		
		
	}
	public int getBulkMemberCount(int bulkId){
		Connection con=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			con=ConnectionPool.getPool().getConnection();
			pstmt=con.prepareStatement("select count(_id) from res_orders where _bulk_id=?");
			pstmt.setInt(1, bulkId);
			rs=pstmt.executeQuery();
			if(rs.next()){
				return rs.getInt(1);
			}
			return 0;
		}catch(Exception e){
			e.printStackTrace();
			return 0;
		}finally{
			try{
				con.close();
			}catch(Exception e){
				
			}	  
		}
	}
	public List<Bulk> getBulkList(){
		Connection con=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			con=ConnectionPool.getPool().getConnection();
			pstmt=con.prepareStatement("select * from res_bulk where year(_send_time)= year(now()) and MONTH(_send_time)=Month(now()) and DAY(_send_time)>=Day(now())");
			rs=pstmt.executeQuery();
			List<Bulk> b=populateBulk(rs);
			if(b.size()==0){
				this.insertBulks(getInitBulks());
				return getBulkList();
			}else{
				return b;
			}
			
		}catch(Exception e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				con.close();
			}catch(Exception e){
				
			}	  
		}
		
		
	}
	private List<Bulk> populateBulk(ResultSet rs) throws SQLException{
		List<Bulk> bulks=new ArrayList<Bulk>();
		while(rs.next()){
			Bulk b=new Bulk();
			b.setId(rs.getInt(1));
			b.setCreaterId(2);
			b.setSendTime(rs.getTimestamp(3));
			b.setDescription(rs.getString(4));
			b.setMaxMember(rs.getInt(5));
			b.setMemberCount(this.getBulkMemberCount(b.getId()));
			bulks.add(b);
		}
		return bulks;
	}
    public void insertBulks(List<Bulk> bulks){
    	Connection con=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		try{
			con=ConnectionPool.getPool().getConnection();
			pstmt=con.prepareStatement("insert into res_bulk(_creater,_send_time,_description,_max_member)values(?,?,?,?)");
			for(Bulk b:bulks){
			pstmt.setInt(1, b.getCreaterId());
			pstmt.setTimestamp(2, b.getSendTime());
			pstmt.setString(3, b.getDescription());
			pstmt.setInt(4, b.getMaxMember());
			pstmt.addBatch();
			}
			pstmt.executeBatch();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			try{
				con.close();
			}catch(Exception e){
				
			}	  
		}
    }
    private List<Bulk> getInitBulks(){
    	List<Bulk> bulks=new ArrayList<Bulk>();
    	Date d=new Date();
    	if(d.getHours()>22) d.setDate(d.getDate()+1);
    	d.setHours(7);
    	d.setMinutes(0);
    	d.setSeconds(0);
    	long begin=d.getTime();
    	for(int i=0;i<31;i++){
    		Bulk b=new Bulk();
    		b.setCreaterId(3);
    		b.setDescription("系统团，1人起团，10人以上9.5折，15人上 9折 ");
    		begin+=1000*60*30;
    		b.setSendTime(new Timestamp(begin));
    		b.setMaxMember(20);
    		bulks.add(b);
    	}
    	return bulks;
    }
}
